﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using MySql.Data.MySqlClient;


namespace MySQLClientTester
{
    class Program
    {
        static readonly string _connStr = String.Format("server={0};uid={1};pwd={2};database={3};Allow User Variables=True;",
                                       "anotherurl.com", "root", "rootpw!", "test");

        [STAThread]
        static void Main(string[] args)
        {
            InitialTest();
            WalkInstaller();

            BusRoutePopulate bp = new BusRoutePopulate();
            bp.DoAdd();

            //bp.Builder();
        }

        private static void WalkInstaller()
        {
            Dictionary<int, string> goodFolderNames = GetGoodFolderNames();

            var ids = new List<int>(goodFolderNames.Keys);
            ids.Sort();

            foreach (var i in ids)
            {
                ExecuteFolderContents(goodFolderNames[i]);
            }
        }

        private static Dictionary<int, string> GetGoodFolderNames()
        {
            var goodFolderNames = new Dictionary<int, string>();
            
            string[] sa = Directory.GetDirectories(@"..\..\..\SQL\");
            foreach (var path in sa)
            {
                string[] saFolders = path.Split('\\');
                if (saFolders.Length != 5)
                    continue;

                int id;
                if (!int.TryParse(saFolders[4], out id))
                    continue;

                goodFolderNames.Add(id, path);
            }
            return goodFolderNames;
        }

        private static void ExecuteFolderContents(string p)
        {
            List<string> filenames = new List<string>(Directory.GetFiles(p));
            filenames.Sort();
            foreach (var filename in filenames)
            {
                ExecuteSQLFile(filename);
            }
        }

        private static void ExecuteSQLFile(string filename)
        {
            var conn = new MySqlConnection(_connStr);
            //open the connection
            conn.Open();

            string sql = File.ReadAllText(filename);

            var cmd = new MySqlCommand(sql, conn);

            try
            {
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine("Wouldn't parse file: " + filename);
                Console.WriteLine(ex.Message);
                Console.ReadLine();
                throw;
            }
            

            //close the connection
            conn.Close();
        }
        /*


         * use laostraveller;

select   bus_stations.name as Stop, places.name as Place, description from bus_stations
left join places 
on bus_stations.place_id = places.place_id
         * 
         * */


        private static void InitialTest()
        {
            //create a MySQL connection with a query string

            //string connStr = String.Format("server={0};uid={1};pwd={2};database={3};Allow User Variables=True;",
            //    "windows7box", "root", "rootpw!", "test");

            // string connStr = String.Format("server={0};uid={1};pwd={2};database={3};Allow User Variables=True;",
            //   "192.168.255.11", "root", "rootpw!", "test");


            //allowMultiQueries=true

            var conn = new MySqlConnection(_connStr);

            //open the connection
            conn.Open();

            DropTable(conn);
            CreateSP(conn);
            RunSP(conn);

            //close the connection
            conn.Close();
        }

        private static void RunSP(MySqlConnection conn)
        {
            var cmd = new MySqlCommand("AsyncSample", conn)
                          {
                              CommandTimeout = 180,
                              CommandType = CommandType.StoredProcedure
                          };
            cmd.ExecuteNonQuery();
        }

        private static void CreateSP(MySqlConnection conn)
        {
            const string sql = "DROP PROCEDURE IF EXISTS AsyncSample;" +
                               "CREATE PROCEDURE AsyncSample() BEGIN " +
                               "SET @x = 0;" +
                               "WHILE @x < 5 DO " +
                               "INSERT INTO ATable VALUES (@x); " +
                               "SET  @x = @x + 1; "+
                               "END WHILE; "+
                               " end;";

            var cmd = new MySqlCommand(sql, conn);
      
            cmd.ExecuteNonQuery();
        }

        private static void DropTable(MySqlConnection conn)
        {
            const string sql = "DROP TABLE IF EXISTS ATable; CREATE TABLE ATable (numVal int)";
            var cmd = new MySqlCommand(sql, conn) {CommandTimeout = 2};
            cmd.ExecuteNonQuery();
        }
    }
}
